package com.lee.mysql.pojo;

import com.lee.mysql.util.JdbcUtil;
import com.lee.mysql.util.SqlUtil;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class SourceData {

    private ResultSet rs;
    private Statement stmt;
    private Connection conn;
    private String db;

    private Map<String, Table> tables = new LinkedHashMap<String, Table>();

    public void close(){
        JdbcUtil.close(rs,stmt,conn);
    }


    public void init(TableDbInfo tableDbInfo) {
        try {
            db = tableDbInfo.getDataBase();

            conn = JdbcUtil.getConn2(tableDbInfo.getDbHost(), tableDbInfo.getDataBase(), tableDbInfo.getDbUsername(), tableDbInfo.getDbPassword(), tableDbInfo.getCharSet());
            stmt = conn.createStatement();
            getTablesFromDb();
            getCreateTables();
            getAllTableColumns();
            getAllTableKeys();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /** 初始化单个/多个表 */
    public void initSomeTable(TableDbInfo tableDbInfo, List<String> tableList) {
        try {
            db = tableDbInfo.getDataBase();

            conn = JdbcUtil.getConn2(tableDbInfo.getDbHost(), tableDbInfo.getDataBase(), tableDbInfo.getDbUsername(), tableDbInfo.getDbPassword(), tableDbInfo.getCharSet());
            stmt = conn.createStatement();
            getSomeFromDb(tableList);
            getCreateTables();
            getAllTableColumns();
            getAllTableKeys();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private void getTablesFromDb() throws SQLException {
        //ResultSet rs = SqlUtil.executeSql(conn, "show tables from " + db);

        String sql = "select TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = '"+db+"' AND TABLE_TYPE = 'BASE TABLE'";
        rs = SqlUtil.executeSql(rs, stmt, conn, sql);

        while (rs.next()) {
            Table table = new Table();
            //table.setTableName(rs.getString("Tables_in_" + db));
            table.setTableName(rs.getString("TABLE_NAME"));
            tables.put(table.getTableName(), table);
        }
    }

    private void getSomeFromDb(List<String> tableList) throws SQLException {
        for (String tableName : tableList) {
            String sql = "SELECT table_name,table_comment FROM information_schema.TABLES WHERE TABLE_SCHEMA= '"+db+"' and TABLE_NAME = '"+tableName+"'";
            rs = SqlUtil.executeSql(rs, stmt, conn, sql);
            while (rs.next()) {
                Table table = new Table();
                table.setTableName(rs.getString("table_name"));
                tables.put(table.getTableName(), table);
            }
        }

    }

    private void getCreateTables() throws SQLException {
        for (Table table : tables.values()) {
            getCreateTable(table);
        }
    }

    private void getCreateTable(Table table) throws SQLException {
        rs = SqlUtil.executeSql(rs, stmt, conn, "show create table " + db + "." + table.getTableName());
        while (rs.next()) {
            table.setCreateTable(rs.getString(2));
        }
    }

    private void getAllTableColumns() throws SQLException {
        for (Table table : tables.values()) {
            getTableColumns(table);
        }
    }

    private void getTableColumns(Table table) throws SQLException {
        rs = SqlUtil.executeSql(rs, stmt, conn, "select COLUMN_NAME,COLUMN_TYPE,IS_NULLABLE,COLUMN_DEFAULT,"
                + "COLUMN_COMMENT,EXTRA from information_schema.columns where TABLE_SCHEMA=" + "'" + db + "'"
                + " and "
                + "TABLE_NAME=" + "'" + table.getTableName() + "'" + " order by ORDINAL_POSITION asc");
        while (rs.next()) {
            Column column = new Column();
            column.setName(rs.getString("COLUMN_NAME"));
            column.setType(rs.getString("COLUMN_TYPE"));
            column.setDefaultValue(rs.getString("COLUMN_DEFAULT"));
            column.setIsNull(rs.getString("IS_NULLABLE"));
            column.setExtra(rs.getString("EXTRA"));
            column.setComment(rs.getString("COLUMN_COMMENT"));
            table.getColumns().put(column.getName(), column);
        }
    }

    private void getAllTableKeys() throws SQLException {
        for (Table table : tables.values()) {
            getTableKeys(table);
        }
    }

    private void getTableKeys(Table table) throws SQLException {
        rs = SqlUtil.executeSql(rs, stmt, conn, "show keys from " + db + "." + table.getTableName());
        Index last = null;
        Index tmp = null;
        while (rs.next()) {
            String keyName = rs.getString("Key_name");
            if (last == null || !keyName.equals(last.getIndexName())) {
                last = new Index();
                last.setIndexName(keyName);
                last.getColumns().add(rs.getString("Column_name"));
                last.setNotUnique(rs.getString("Non_unique"));
                table.getIndexes().put(last.getIndexName(), last);
            } else {
                // 表明这两个key在同一索引中
                last.getColumns().add(rs.getString("Column_name"));
            }
        }
    }



    public String getDb() {
        return db;
    }

    public void setDb(String db) {
        this.db = db;
    }

    public ResultSet getRs() {
        return rs;
    }

    public void setRs(ResultSet rs) {
        this.rs = rs;
    }

    public Statement getStmt() {
        return stmt;
    }

    public void setStmt(Statement stmt) {
        this.stmt = stmt;
    }

    public Connection getConn() {
        return conn;
    }

    public void setConn(Connection conn) {
        this.conn = conn;
    }

    public Map<String, Table> getTables() {
        return tables;
    }

    public void setTables(Map<String, Table> tables) {
        this.tables = tables;
    }
}
